﻿using HuaCheng.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace HuaCheng.DataAccess
{
    /// <summary>
    /// 对设备信息的数据库操作
    /// </summary>
    public class DeviceHelper
    {
        /// <summary>
        /// 查询当前所有设备
        /// </summary>
        /// <returns></returns>
        public static List<Device> GetTreeNodeList()
        {
            //string sqlStr = "select dev_type,dev_name,GROUP_CONCAT(dev_ip separator ',') as devicelist from devices GROUP BY dev_type";
            string sqlStr = "select dev_mac,cabinet_id,mid_id,dev_id,dev_ip,dev_type,dev_name,dev_delete from devices";
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
            List<Device> devices = new List<Device>();
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Device dev = new Device()
                            {
                                DevMac = Convert.ToString(reader["dev_mac"]),
                                GID = Convert.ToInt32(reader["cabinet_id"]),
                                MID = Convert.ToInt32(reader["mid_id"]),
                                DevId = Convert.ToString(reader["dev_id"]),
                                DevIP = Convert.ToString(reader["dev_ip"]),
                                DevType = Convert.ToString(reader["dev_type"]),
                                DevName = Convert.ToString(reader["dev_name"]),                                
                                DevDelete = Convert.ToBoolean(reader["dev_delete"])
                            };
                            devices.Add(dev);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return devices;
        }
        /// <summary>
        /// 新增设备连接
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="devtype"></param>
        public static void ModifyDevices(int gid ,int mid,string ip)
        {
            try
            {
                string sql = "select * from devices where cabinet_id=@gid and mid_id=@mid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                var dt = DBAccessManager.SqlDBAccess.ExecuteScalar(cmd);
                if (dt != null)//如果记录不存在则插入数据库
                {
                    try
                    {
                        string secSql = "update devices set dev_ip=@ip where cabinet_id=@gid and mid_id=@mid";
                        DbCommand secCmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, secSql);
                        secCmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@newip", ip));
                        secCmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@del", false));
                        DBAccessManager.SqlDBAccess.ExecuteNonQuery(secCmd);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message + ex.StackTrace);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message + e.StackTrace);
            }
            //try
            //{
            //    string sql = "select dev_ip from devices where dev_ip=@ip and dev_type=@type";
            //    DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            //    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
            //    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@type", devtype));
            //    var dt = DBAccessManager.SqlDBAccess.ExecuteScalar(cmd);
            //    if (dt == null)//如果记录不存在则插入数据库
            //    {
            //        try
            //        {
            //            string secSql = "insert into devices(dev_ip,dev_type,dev_delete) values(@newip,@newtype,@del)";
            //            DbCommand secCmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, secSql);
            //            secCmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@newip", ip));
            //            secCmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@newtype", devtype));
            //            secCmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@del", false));
            //            DBAccessManager.SqlDBAccess.ExecuteNonQuery(secCmd);
            //        }
            //        catch (Exception ex)
            //        {
            //            Console.WriteLine(ex.Message + ex.StackTrace);
            //        }

            //    }
            //}
            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message + e.StackTrace);
            //}

        }
        /// <summary>
        /// 查询所有设备类型
        /// </summary>
        /// <returns></returns>
        public static List<string> RetrieveDevTypes()
        {
            string strSql = "select distinct dev_type from devices";
            DbCommand dbcmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, strSql);
            List<string> allDevs = new List<string>();
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(dbcmd))
                {
                    if(reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            allDevs.Add(reader[0].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return allDevs;
        }
        /// <summary>
        /// 添加中位机信息
        /// </summary>
        /// <param name="dev"></param>
        public static void AddDevInfo(Device dev)
        {
            string sql = "INSERT INTO devices (dev_mac,cabinet_id,mid_id,dev_id,dev_ip,dev_type,dev_name,dev_delete) VALUES (@devmac,@gid,@mid,@devid,@devip,@devtype,@devname,@devdel)"
            + " ON DUPLICATE KEY UPDATE "
            + "cabinet_id = VALUES(cabinet_id),"
            + "mid_id = VALUES(mid_id),"
            + "dev_id = VALUES(dev_id),"
            + "dev_ip = VALUES(dev_ip),"
            + "dev_type = VALUES(dev_type),"
            + "dev_name = VALUES(dev_name),"
            + "dev_delete = VALUES(dev_delete)";
            //string sql = "insert into devices(dev_mac,cabinet_id,mid_id,dev_id,dev_ip,dev_type,dev_name,dev_delete) values (@devmac,@gid,@mid,@devid,@devip,@devtype,@devname,@devdel)";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devmac", dev.DevMac));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", dev.GID));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", dev.MID));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devid", dev.DevId));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devip", dev.DevIP));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devtype", dev.DevType));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devname", dev.DevName));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devdel", dev.DevDelete));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 根据设备MAC和设备ID更新设备ip
        /// </summary>
        /// <param name="dev"></param>
        public static bool UpdateIpByMac(string ip,string devmac,string devid)
        {
            bool isBelonged = false;
            try
            {
                string sql = "update devices set dev_ip=@ip where dev_mac=@devmac and dev_id=@devid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devmac", devmac));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devid", devid));
                int i = DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                if(i>0)
                {
                    isBelonged = true;
                }                
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return isBelonged;
        }
        /// <summary>
        /// 根据ip获得设备配置包
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="configData"></param>
        /// <returns></returns>
        public static CommandProtocol.DeviceConfig GetConfigByIp(string ip,CommandProtocol.DeviceConfig configData)
        {
            try
            {
                string sql = "select cabinet_id,mid_id from devices where dev_ip = @ip";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            configData.Port = 17002;
                            configData.Gid = Convert.ToInt32(reader["cabinet_id"])-1;
                            configData.Mid = Convert.ToInt32(reader["mid_id"])-1;
                        }
                    }
                }
            }
            catch(Exception e)
            {
                Console.WriteLine(e.StackTrace + e.Message);
            }
            return configData;
        }
        /// <summary>
        /// 查询所有中位机信息
        /// </summary>
        /// <returns></returns>
        public static List<Device> queryMachineInfo()
        {
            List<Device> devices = new List<Device>();
            string strSql = "select * from devices";
            DbCommand dbcmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, strSql);
            List<string> allDevs = new List<string>();
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(dbcmd))
                {
                    if(reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Device device = new Device()
                            {
                                DevMac = Convert.ToString(reader["dev_mac"]),
                                GID = Convert.ToInt32(reader["cabinet_id"]),
                                MID = Convert.ToInt32(reader["mid_id"]),                             
                                DevId = Convert.ToString(reader["dev_id"]),
                                DevIP = Convert.ToString(reader["dev_ip"]),
                                DevType = Convert.ToString(reader["dev_type"]),
                                DevName = Convert.ToString(reader["dev_name"]),
                                DevDelete = Convert.ToBoolean(reader["dev_delete"])
                            };
                            devices.Add(device);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return devices;
        }
        public static void deleteDev(string devMac)
        {
            try
            {
                string sqlStr = "delete from devices where dev_mac = @devmac";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devmac", devMac));
                DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
        }
        public static Device QueryDeviceByIP(string ip)
        {
            Device device = null;
            string strSql = "select * from devices where dev_ip=@ip";
            DbCommand dbcmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, strSql);            
            try
            {
                dbcmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(dbcmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            device = new Device()
                            {
                                DevMac = Convert.ToString(reader["dev_mac"]),
                                GID = Convert.ToInt32(reader["cabinet_id"]),
                                MID = Convert.ToInt32(reader["mid_id"]),
                                DevId = Convert.ToString(reader["dev_id"]),
                                DevIP = Convert.ToString(reader["dev_ip"]),
                                DevType = Convert.ToString(reader["dev_type"]),
                                DevName = Convert.ToString(reader["dev_name"]),
                                DevDelete = Convert.ToBoolean(reader["dev_delete"])
                            };                           
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return device;
        }
    }
}
